# -*- coding: utf-8 -*-
"""
@author: wyb
@date: 2023/12/8
@time: 9:08
@desc: Sends emails based on payment status in spreadsheet
"""
import openpyxl, smtplib, sys

wb = openpyxl.load_workbook('duesRecords.xlsx')
sheetNames = wb.sheetnames
print(sheetNames)

sheet1 = wb["Sheet1"]
lastCol = sheet1.max_column
lastMonth = sheet1.cell(row=1, column=lastCol).value
print(lastMonth)

email_address = "wdhwyb@hotmail.com"
password = "kdmicdbrsbquwpql"

# 连接邮箱服务器
# # outlook.office365.com
# mail = imaplib.IMAP4_SSL("outlook.office365.com")  # 请根据你的邮箱提供商修改主机名
# mail.login(email_address, password)

unpaidMembers = {}
for r in range(2, sheet1.max_row + 1):
    paidStatus = sheet1.cell(row=r, column=lastCol).value
    if paidStatus != 'paid':
        name = sheet1.cell(row=r, column=1).value
        email = sheet1.cell(row=r, column=2).value
        unpaidMembers[name] = email
smtpObj = smtplib.SMTP('outlook.office365.com', 587)
smtpObj.ehlo()
smtpObj.starttls()
smtpObj.login(email_address, password)

for name, email in unpaidMembers.items():
    body = f"Subject: {lastMonth} dues unpaid.\nDear{name},\nRecords show that you have not paid dues for {lastMonth}.Please make this payment as soon as possible.Thank you!'"
    print(f'sending mail to {email}')
    status = smtpObj.sendmail('wdhwyb@hotmail.com', email, body)
    if status != {}:
        print(f'there was a problem sending email to {email}:{status}')
smtpObj.quit()
